package com.ttl.bc.ic.stkissue.db;

import hibernate.util.HibernateUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionForm;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.classic.Session;

import com.ttl.bc.ap.stkrequest.BCStkRequestBean;
import com.ttl.bc.ap.stkrequest.BCStkRequestSubBean;
import com.ttl.bc.ic.item.BCItemBean;
import com.ttl.bc.ic.stkissue.bean.BCStkIssueBean;
import com.ttl.bc.ic.stkissue.bean.BCStkIssueSubBean;
import com.ttl.bc.ic.stkissue.bean.ITTL_OutStock_MinDate;
import com.ttl.bc.master.issuetype.BCIssueTypeBean;
import com.ttl.bc.master.project.BCProjectBean;
import com.ttl.bc.processstock.bean.ProcessStockBean;
import com.ttl.bc.processstock.db.ProcessStockDAO;
import com.ttl.bc.stock.request2.action.BCStkIssue2Form;
import com.ttl.bc.stock.request2.bean.BCStkIssue2;
import com.ttl.bc.stock.request2.bean.BCStkIssueSub2;
import com.ttl.common.db.ADAO;
import com.ttl.common.util.CommonUtils;

public class BCStkIssueDAO extends ADAO {

	@SuppressWarnings("unchecked")
	public void delete(String s) {
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			session.beginTransaction();
			
			Query q = session.createQuery(s);
			List list = q.list();
			for(int i=0; i<list.size(); i++) {
				BCStkIssueBean b = (BCStkIssueBean)list.get(i);
				session.delete(b);
			}			
			
			session.getTransaction().commit();						
						
		}catch(Exception e) {
			System.out.println(e.toString());			
		}finally {
			session.close();
		}

	}	
	
	@SuppressWarnings("unchecked")
	public List find(String hql,int maxResults) {
		List result = null;
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();			
			session.beginTransaction();
			
			Query q = session.createQuery(hql);
			if(maxResults>0) {
				q.setMaxResults(maxResults);
			}
			result = q.list();
			
			session.getTransaction().commit();						
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		return result;
	}	
	
	@SuppressWarnings("unchecked")
	public boolean updatePrice(Object obj) {
						
		List result = null;
		
		ArrayList al = (ArrayList)obj; 
		BCStkIssueSubBean sub = (BCStkIssueSubBean)al.get(0);
				
		String hql = "FROM BCStkIssueSubBean WHERE refNo = '"+sub.getRefNo()+"'";		
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
										
			session.beginTransaction();
			
			Query q = session.createQuery(hql);
			result = q.list();
			
			for(int i=0; i<result.size(); i++) {
				BCStkIssueSubBean sResult = (BCStkIssueSubBean)q.list().get(i);
				for(int j=0; j<al.size(); j++) {
					sub = (BCStkIssueSubBean)al.get(j);
					if(sResult.getItemCode().equals(sub.getItemCode())) {
						
						sResult.setPrice(sub.getPrice());
						sResult.setAmount(sub.getAmount());
						session.update(sResult);						
					}				
				}
			}
			
			session.getTransaction().commit();						
			return true;
		}catch(Exception e) {			
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean swapToStkRequest(HttpServletRequest request, String docNo1) {
		
		String hql = "FROM BCStkIssue2 WHERE docNo = '"+docNo1+"'";		
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
										
			session.beginTransaction();
			
			Query q = session.createQuery(hql);
			List result = q.list();
			
			if(result!=null && result.size()>0) {
				BCStkIssue2 issue2 = (BCStkIssue2)result.get(0);
				
				BCStkRequestBean req = new BCStkRequestBean();
				req.setDocNo(CommonUtils.getMaxStkRequestDocNo(issue2.getDocNo().substring(0, 5)));
				req.setDocDate(issue2.getDocDate());
				req.setDepartCode(issue2.getDepartCode());
				req.setMyDescription(issue2.getMyDescription());
				req.setProjectCode(issue2.getProjectCode());		
				req.setCreatorCode(issue2.getCreatorCode());
				req.setCreateDateTime(issue2.getCreateDateTime());
									
				req.setIsConfirm(1);
				req.setBillStatus(1);				
				req.setIsCancel(0);
				req.setIttl_deptRequest(issue2.getiTTL_deptPay());		
				req.setApCode("000");
				req.setIttl_status(issue2.getiTTL_status());		
				req.setIttl_userLevel(issue2.getIttl_userLevel());
				
				HashSet s = new HashSet();
				
				for (Iterator iterator = issue2.getBcStkIssueSub2s().iterator(); iterator
						.hasNext();) {
					BCStkIssueSub2 sub2 = (BCStkIssueSub2) iterator.next();
					
					BCStkRequestSubBean reqSub = new BCStkRequestSubBean();
					reqSub.setIttl_status("0");
					reqSub.setDepartCode(req.getDepartCode());
					reqSub.setDocDate(req.getDocDate());							
					reqSub.setItemCode(sub2.getItemCode());
					reqSub.setItemName(sub2.getItemName());
					
					//reqSub.setIttl_itemCode_ttl2(reqForm.getShortName1());
					
					reqSub.setQty(sub2.getQty());
					reqSub.setRemainQty(sub2.getQty());
					
/*					System.out.println("price="+sub2.getPrice());
					reqSub.setPrice(Float.valueOf(""+sub2.getPrice()));
					System.out.println("amount="+sub2.getAmount());
					reqSub.setAmount(Float.valueOf(""+sub2.getAmount()));*/
					
//					BCItemUnitBean itemU = new BCItemUnitBean();
//					itemU.setCode(sub2.getBcitemUnit().getCode());					
//					reqSub.setBcitemUnit(itemU);
					reqSub.setUnitCode(sub2.getUnitCode());
					
					reqSub.setLineNumber(sub2.getLineNumber());
					reqSub.setIttl_unitName(sub2.getIttl_unitName());
					reqSub.setIttl_division(sub2.getIttl_division());								
					
					reqSub.setProjectCode(req.getProjectCode());
					reqSub.setCreatorCode(req.getCreatorCode());
					reqSub.setCreateDateTime(req.getCreateDateTime());			
					reqSub.setBcStkRequest(req);
					
					s.add(reqSub);
				}
				req.setBcStkRequestSubs(s);
				
				session.save(req);
				session.delete(issue2);
				
				System.out.println("เลขที่ใบขอเบิกเดิม :"+issue2.getDocNo());
				System.out.println("เลขที่ใบขอซื้อใหม่ :"+req.getDocNo());
				
				request.setAttribute("docNoMessage", "เลขที่ :"+issue2.getDocNo()+" เปลี่ยนเป็น :"+req.getDocNo());
			}
			
			session.getTransaction().commit();						
			return true;
		}catch(Exception e) {			
			System.out.println(e);
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean swapToStkIssue2(HttpServletRequest request, String docNo2) {
		String hql = "FROM BCStkRequestBean WHERE docNo = '"+docNo2+"'";		
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
										
			session.beginTransaction();
			
			Query q = session.createQuery(hql);
			List result = q.list();
			
			if(result!=null && result.size()>0) {
				BCStkRequestBean req = (BCStkRequestBean)result.get(0);
				
				BCStkIssue2 issue2 = new BCStkIssue2();
				issue2.setDocNo(CommonUtils.getMaxDocNo(req.getDocNo().substring(0, 5)));
				issue2.setDocDate(req.getDocDate());
				issue2.setDepartCode(req.getDepartCode());
				issue2.setMyDescription(req.getMyDescription());
				issue2.setProjectCode(req.getProjectCode());		
				issue2.setCreatorCode(req.getCreatorCode());
				issue2.setCreateDateTime(req.getCreateDateTime());
									
				
				issue2.setBillStatus(0);				
				issue2.setIsCancel(0);
				issue2.setiTTL_deptPay(req.getIttl_deptRequest());		
				
				issue2.setiTTL_status(req.getIttl_status());		
				issue2.setIttl_userLevel(req.getIttl_userLevel());
				issue2.setPersonCode(req.getCreatorCode());
				issue2.setIsCompleteSave(1);
				
				BCIssueTypeBean issueType = new BCIssueTypeBean();
				issueType.setCode("01");		
				issue2.setBcIssueTypeBean(issueType);				   							
				issue2.setIssueType("01");
				
				
				HashSet s = new HashSet();
				
				for (Iterator iterator = req.getBcStkRequestSubs().iterator(); iterator
						.hasNext();) {
					BCStkRequestSubBean reqSub = (BCStkRequestSubBean) iterator.next();
					
					BCStkIssueSub2 issueSub2 = new BCStkIssueSub2();

					issueSub2.setDepartCode(issue2.getDepartCode());
					issueSub2.setDocDate(issue2.getDocDate());
					issueSub2.setIssueType(issue2.getIssueType());				
					issueSub2.setItemCode(reqSub.getItemCode());
					issueSub2.setItemName(reqSub.getItemName());
					issueSub2.setQty(Float.valueOf(reqSub.getQty()));
					issueSub2.setRemainQty(Float.valueOf(reqSub.getQty()));
					
//					BCItemUnitBean itemU = new BCItemUnitBean();
//					itemU.setCode(reqSub.getBcitemUnit().getCode());
//					issueSub2.setBcitemUnit(itemU);
					issueSub2.setUnitCode(reqSub.getUnitCode());
					issueSub2.setWhCode("10");
					
					issueSub2.setLineNumber(reqSub.getLineNumber());
					issueSub2.setIttl_unitName(reqSub.getIttl_unitName());
					issueSub2.setIttl_division(reqSub.getIttl_division());								
					
//					BCShelfBean shelf = new BCShelfBean();
//					shelf.setCode("10");
//					issueSub2.setBcShelf(shelf);	
					issueSub2.setShelfCode("10");
					issueSub2.setPersonCode(issue2.getPersonCode());
					issueSub2.setProjectCode(issue2.getProjectCode());
					issueSub2.setCreatorCode(issue2.getPersonCode());
					issueSub2.setCreateDateTime(issue2.getCreateDateTime());	
					issueSub2.setBcStkIssue2(issue2);					
					
					s.add(issueSub2);
				}
				issue2.setBcStkIssueSub2s(s);
				
				session.save(issue2);
				session.delete(req);
				
//				System.out.println("stock request old :"+req.getDocNo());
//				System.out.println("stock issue2 new:"+issue2.getDocNo());
				request.setAttribute("docNoMessage", "เลขที่ :"+req.getDocNo()+" เปลี่ยนเป็น :"+issue2.getDocNo());
			}
			
			session.getTransaction().commit();						
			return true;
		}catch(Exception e) {			
			System.out.println(e);
			return false;
		}finally {
			session.close();
		}
		
	}

	public ITTL_OutStock_MinDate getMinDate() {
		ITTL_OutStock_MinDate m = new ITTL_OutStock_MinDate();
		
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			session.beginTransaction();
			String hql= "FROM ITTL_OutStock_MinDate";
			System.out.println(hql);
			Query q = session.createQuery(hql);
			List<ITTL_OutStock_MinDate> list = q.list();
			for(int i=0; i<list.size(); i++) {
				ITTL_OutStock_MinDate min = (ITTL_OutStock_MinDate)list.get(i);
				m.setMinDate(min.getMinDate());
			}			
			
			session.getTransaction().commit();						
						
		}catch(Exception e) {
			System.out.println(e.toString());			
		}finally {
			session.close();
		}
		
		if(m.getMinDate()==null) {
			m.setMinDate(new Date());
		}
		
		return m;
	}

	@SuppressWarnings("finally")
	public boolean updateClosedDate(ActionForm form, HttpServletRequest request) {
		String minDate = request.getParameter("minDate");
		ITTL_OutStock_MinDate m = new ITTL_OutStock_MinDate();
		m.setMinDate(CommonUtils.convertStringToDate(minDate));
		
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			session.beginTransaction();
			String hql= "FROM ITTL_OutStock_MinDate";
			System.out.println(hql);
			Query q = session.createQuery(hql);
			List<ITTL_OutStock_MinDate> list = q.list();
			for(int i=0; i<list.size(); i++) {
				session.delete(list.get(i));
			}			
			session.save(m);
			
			session.getTransaction().commit();						
			
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());	
			return false;
		}finally {
			
			session.close();
			
		}
	}

	@SuppressWarnings("rawtypes")
	public boolean approveConfirmCancel(HttpServletRequest request,
			HttpServletResponse response) {
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();			
			
			BCStkIssue2Form stkIssue2Form = BCStkIssueBO.setForm(request);				
			
			BCProjectBean proj = new BCProjectBean();
			proj.setCode(stkIssue2Form.getProjectCode());
			
			BCIssueTypeBean issueType = new BCIssueTypeBean();
			issueType.setCode(request.getParameter("issueType"));
			
			// update สถานะของใบ พ.1 นั้น -> รอเบิกแล้ว billStatus = 0, และ ittl_status =1 เพื่อ checked การเบิก, ittl_status=0 ยังไม่ได้เบิก(ยกเลิกเบิก) 
			BCStkIssue2 issue2 = BCStkIssueBO.setBCStkIssue2(stkIssue2Form);	
			issue2.setBcIssueTypeBean(issueType);
			issue2.setiTTL_status("0");
			issue2.setBillStatus(0);
			Iterator itSub = issue2.getBcStkIssueSub2s().iterator();
			while (itSub.hasNext()) {
				BCStkIssueSub2 issueSub2 = (BCStkIssueSub2) itSub.next();
				issueSub2.setIttl_status("0");
			}
			
			// เพื่อให้ค้างในช่องค้นหา docNo
			request.setAttribute("docNoFind", issue2.getDocNo());
			
			//==================================== start : dao.update(issue2.getDocNo(),issue2);
			String hql = "FROM BCStkIssue2 h where h.docNo = '"+issue2.getDocNo()+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			
                 
            for(int i=0; i<resultList.size(); i++){
            	BCStkIssue2 h = (BCStkIssue2)resultList.get(i);       
                session.delete(h);
             }            
            
            BCStkIssue2 stk = (BCStkIssue2)issue2;
            Iterator it = stk.getBcStkIssueSub2s().iterator();
            while (it.hasNext()) {
				BCStkIssueSub2 sub = (BCStkIssueSub2) it.next();
				
				hql = "FROM BCItemBean WHERE code='"+sub.getItemCode()+"'";
	            org.hibernate.Query q2 = session.createQuery(hql);
	            List resultList2 = q2.list();							
				
	            if( resultList2!=null & resultList2.size()>0) {
	            	BCItemBean bean = (BCItemBean)resultList2.get(0);
					sub.setPrice(bean.getLastBuyPrice());
					sub.setAmount(sub.getQty()*sub.getPrice());
	            }	            
			}
            
			session.save(issue2);			
			//==================================== end : dao.update(issue2.getDocNo(),issue2);
			// ลบรายการออกจาก table: BCStkIssue
			hql = "FROM BCStkIssueBean s WHERE docNo like '%"+issue2.getDocNo()+"%'";
			
			//BCStkIssueDAO dao3 = new BCStkIssueDAO();
			//ArrayList al = (ArrayList)dao3.find(hql);
			
			Query q2 = session.createQuery(hql);
			List al = q2.list();
			
			if(al!=null && al.size()>0){		
				BCStkIssueBean issue = (BCStkIssueBean)al.get(0);
				// ใส่ข้อมูล itemCode รายการนั้นใน table: processStock			
				//ProcessStockDAO p = new ProcessStockDAO();
		    	Iterator it2 = issue.getBcStkIssueSubs().iterator();
		    	
		        while(it2.hasNext()) {
		        	BCStkIssueSubBean sub = (BCStkIssueSubBean)it2.next();
		        	
		        	ProcessStockBean bean = new ProcessStockBean();
		        	bean.setItemCode(sub.getItemCode());
		        	bean.setDocDate(sub.getDocDate());
		        	bean.setProcessFlag(0);
		        	bean.setFlowStatus(1);
		        	bean.setProcessType(14);
		        	bean.setProcessCase(3);// สำหรับ BCStkIssue บันทึกเอกสารให้ =1, ลบเอกสารให้=3
		        	
		        	session.save(bean);
		        			        			    		         	
		        }	        
			}
	        // ลบรายการใน BCStkIssue
			
			//start : dao3.delete(hql);
			Query q3 = session.createQuery(hql);
			List list = q3.list();
			for(int i=0; i<list.size(); i++) {
				BCStkIssueBean b = (BCStkIssueBean)list.get(i);
				session.delete(b);
			}
			//end : dao3.delete(hql);
			
			session.getTransaction().commit();
			
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());	
			return false;
		}finally {
			
			session.close();
			
		}	
	}
}
